	package browser.service;

	import browser.dao.UserInfo;
	import java.io.*;
	import java.sql.*;
	import java.util.ArrayList;
	import javax.servlet.*;
	import javax.servlet.http.*;

	/**
	 * Servlet that will display query results
	 *
	 */
	public class QueryTable extends HttpServlet {

		public static Connection conn;
		public static Statement stmt;

		public void doGet(HttpServletRequest request,
				HttpServletResponse response) throws ServletException,
				IOException {

			doPost(request, response);
		}

		public void doPost(HttpServletRequest request,
				HttpServletResponse response) throws ServletException,
				IOException {

			response.setContentType("text/html");
			// response.setContentType("application/vnd.ms-excel");
			PrintWriter out = response.getWriter();

			// Check if session is expired
			if (request.getSession(false) == null
					|| (Boolean) request.getSession(false).getAttribute(
							"expired") == true) {
				out.println("Session expired");
				return;
			}

			String[] uid = request.getParameterValues("uid");
			String[] uacc = request.getParameterValues("uAccess");
			String[] tname = request.getParameterValues("tName");
			String[] qcols = request.getParameterValues("qCols");

			try {
				Class.forName("oracle.jdbc.driver.OracleDriver");
			} catch (ClassNotFoundException e) {
				out.println("Error loading the Driver:" + e.getMessage());
				return;
			}

			Connection conn = null;
			UserInfo user = MyUtilities.getUserInfo(uid[0]);

			if ((user.getUaccess() == null)
					|| (!uacc[0].equals(user.getUaccess()))) {
				out.print("Please login first");
				return;
			}
			try {
				conn = DriverManager.getConnection("jdbc:oracle:thin:"
						+ user.getUid() + "/" + user.getPassword()
						+ "@oraserver.cs.luc.edu:1521:cs");
			} catch (SQLException e1) {
				out.println("Error connecting to Oracle:" + e1.getMessage());
				return;
			}
			if (conn == null) {
				out.println("Null Connection");
				return;
			}

			Statement stmt = null;
			try {
				stmt = conn.createStatement();
			} catch (SQLException e) {
				out.println("createStatement " + e.getMessage());
				try {
					conn.close();
				} catch (SQLException e2) {
				}
				;
				return;
			}

			out.println("<html>");
			out.println("  <head><title>" + tname[0]);
			out.println("  </title><script type=\"text/javascript\" src=\"dojo.js\"></script>");
			out.println("<script type=\"text/javascript\" src=\"modify.js\"></script><link type=\"text/css\" rel=\"stylesheet\" href=\"main.css\"/></head>");
			out.println("  <body>");
			out.println("    <p>");
			out.println("    <center>");
			out.println("      <h2>" + tname[0] + "</h2>");

			ResultSet rset = null;
			ResultSet pkset = null;
			String qcolnames = "";
			ArrayList<String> pkcol = new ArrayList<String>();
			String query = "select * from " + tname[0];
			String export = "";
			if (qcols == null) {
				export = "select * from " + tname[0];
			} else {
				for (int i = 0; i < qcols.length; i++) {
					qcolnames += qcols[i] + ",";
				}
				export = "select "
						+ qcolnames.substring(0, qcolnames.lastIndexOf(','))
						+ " from " + tname[0];
			}


			try {
				out.println("<div id=\"pkcols\">");
				rset = stmt.executeQuery(query);
				pkset = conn.getMetaData().getPrimaryKeys(null, null, tname[0]);
				if (pkset != null) {
					while (pkset.next()) {
						pkcol.add(pkset.getString(4));
					}
					for (int i = 0; i < pkcol.size(); i++) {
						out.println("<input type=\"hidden\" class=\"col\" value=\"" + pkcol.get(i) + "\">");
					}
				}
				out.println("</div>");

				if(qcols == null){
					ResultSetMetaData meta = rset.getMetaData();
					qcols = new String[meta.getColumnCount()];
					for(int i=0; i<meta.getColumnCount(); i++){
						qcols[i] = meta.getColumnName(i+1);
					}
				}
			} catch (SQLException e) {
				out.println("executeQuery " + e.getMessage());
				return;
			}

			try {

				out.println("<form action=\"/DatabaseBrowser/ModifyRow\" method=\"post\">");
				out.println("<input type=\"hidden\" name=\"uid\" value=\"" + uid[0] + "\">");
				out.println("<input type=\"hidden\" name=\"uAccess\" value=\""+ uacc[0] + "\">");
				out.println("<input type=\"hidden\" name=\"tName\" id=\"tName\" value=\"" + tname[0] + "\">");
				out.println("<input type=\"hidden\" name=\"submitbutton\" id=\"submitbutton\" value=\"\">");

				out.println("<table border=2 id=\"resulttb\">");
				out.println("<tr>");
				for (int i = 0; i < qcols.length; i++) {
					out.println("<th>" + qcols[i] + "</th>");
				}
				out.println("<th>Modify?</th>");
				out.println("</tr>");
				int k = 0;
				while (rset.next()) {
					out.println("<tr>");
					for (int i = 0; i < qcols.length; i++) {
						out.println("<td>" + rset.getString(qcols[i]) + "</td>");
					}
					for (int i = 0; i < pkcol.size(); i++) {
						out.println("<input type=\"hidden\" class=\""
								+ pkcol.get(i) + "\" value=\""
								+ rset.getString(pkcol.get(i)) + "\">");
					}
					out.println("<td><input type=\"radio\" name=\"modify\" class=\"modify\" value=\""
									+ k + "\" /></td>");
					out.println("</tr>");
					k++;
				}
				out.println("</table>");
				out.println("<input type=\"submit\" value=\"Insert Row\" id=\"insertRow\" />");
				out.println("</form>");
				out.println("<input type=\"submit\" value=\"Update Row\" id=\"updateRow\" />");
				out.println("<input type=\"submit\" value=\"Delete Row\" id=\"deleteRow\" />");

			} catch (SQLException e) {
				e.printStackTrace();
			}

			out.println("</br>");
			out.println("<form action=\"/DatabaseBrowser/ExportData.xls\" method=\"post\">");
			out.println("<input type=\"hidden\" name=\"uid\" value=\"" + uid[0] + "\">");
			out.println("<input type=\"hidden\" name=\"uAccess\" value=\"" + uacc[0] + "\">");
			out.println("<input type=\"hidden\" name=\"query\" value=\""
					+ java.net.URLEncoder.encode(export, "UTF-8") + "\">");
			out.println("<input type=\"submit\" value=\"Export to Excel\">");
			out.println("</form>");

			out.println("    </center>");
			out.println("    </p>");
			out.println("  </body>");
			out.println("</html>");

			try {
				stmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			out.close();
		}
	}
